package com.dy.yunying.biz.dao.doris.impl;

import com.dy.yunying.api.constant.Constant;
import com.dy.yunying.api.datacenter.dto.AdOverviewDto2;
import com.dy.yunying.api.datacenter.vo.AdAgentAccountVo;
import com.dy.yunying.api.datacenter.vo.AdDataAnalysisVO;
import com.dy.yunying.api.enums.DataReportEnum;
import com.dy.yunying.biz.config.YunYingDorisTableProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName AdAnalysisRepairDorisDao
 * @Description todo
 * @Author nieml
 * @Time 2022/12/29 16:40
 * @Version 1.0
 **/
@Slf4j
@Component(value = "adAnalysisRepairDorisDao")
public class AdAnalysisRepairDorisDao {

	@Resource(name = "dataLayerDorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Resource
	private YunYingDorisTableProperties yunYingDorisTableProperties;

	@Resource
	private DataLayerOfflineRepairDao dataLayerOfflineRepairDao;


	final private String indentStr = "		";
	final private String indentStr1 = "		";
	final private String indentStr2 = "				";
	final private String indentStr3 = "						";

	/**
	 * 广告数据分析表总数查询
	 *
	 * @param req
	 * @return
	 */
	public Long countDataTotal(AdOverviewDto2 req) {
		final StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(this.getResultSql(req));
		countSql.append("    ) t\n");
		return dorisTemplate.queryForObject(countSql.toString(), Long.class);
	}

	/**
	 * 广告数据分析表分页列表查询
	 *
	 * @param req
	 * @return
	 */
	public List<AdDataAnalysisVO> list(AdOverviewDto2 req) {
		final StringBuilder sql = this.getResultSql(req);

		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}

		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}

		log.info("广告数据分析表分列表页查询SQL起点: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<AdDataAnalysisVO> list = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(AdDataAnalysisVO.class));
		long end = System.currentTimeMillis();
		log.info("广告数据分析表分页列表查询耗时: {}ms", end - start);
		return list;
	}

	/**
	 * 处理请求参数：代理商等
	 * */
	private void dealReqParams(AdOverviewDto2 req){
		// 根据代理商查询对应的广告账户
		StringBuilder agrntAccountList = new StringBuilder();
		if (StringUtils.isNotBlank(req.getAgentIdArr())) {
			StringBuilder adAccountSql = new StringBuilder();
			adAccountSql.append("SELECT advertiser_id advertiserId FROM ").append(yunYingDorisTableProperties.getDim3399MysqlAdAccountAgent()).append(" t where t.agent_id in  (").append(req.getAgentIdArr()).append(") and TO_DATE(t.effective_time) <= TO_DATE(now()) and TO_DATE(t.invalid_time) >= TO_DATE(now())");
			List<AdAgentAccountVo> listAdvertiser = dorisTemplate.query(adAccountSql.toString(), new Object[]{},
					new BeanPropertyRowMapper<>(AdAgentAccountVo.class));
			listAdvertiser.forEach(data -> {
				if (StringUtils.isNotBlank(data.getAdvertiserId())) {
					agrntAccountList.append(",").append(data.getAdvertiserId());
				}
			});
			if (StringUtils.isNotBlank(agrntAccountList)) {
				String agAccountList = agrntAccountList.substring(1);
				req.setAgentAdvertiserIdArr(agAccountList);
			}
			if (StringUtils.isBlank(agrntAccountList)) {
				req.setAgentAdvertiserIdArr(agrntAccountList.toString());
			}
		}
	}

	private StringBuilder getResultSql(AdOverviewDto2 req) {
		StringBuilder sql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);

		//处理请求参数：代理商等
		dealReqParams(req);

		sql.append("SELECT\n");
		sql.append("    ").append(periodSql).append(" AS period ").append(selectDimFieldsSql);
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("adid")) {
			sql.append("budget, adidName, ctype, campaignId, campaignName, startTime, endTime, createTime, updateTime, unionVideoType, inventoryType, deliveryRange, pricing, advertid, adAccountName, status, optStatus, ");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("advertiserid") && !queryColumn.contains("adid")) {
			sql.append("advertiserid advertid, ");
		}
		sql.append("showRatio,\n");
		sql.append("      usrnamenums,-- 新增设备注册数").append(" \n");
		sql.append("      uuidnums, -- 新增设备").append(" \n");
		sql.append("      retention2, -- 次留").append(" \n");
		sql.append("      usrpaynamenums AS paydevice1, -- 新增设备付费数").append(" \n");
		sql.append("      newdevicefees, -- 新增充值实付金额（分成前）").append(" \n");
		sql.append("      newdevicesharfee, -- 新增充值实付金额（分成后）").append(" \n");
		sql.append("      newdevicegivemoney, -- 新增充值代金券金额（分成前）").append(" \n");
		sql.append("      newdeviceshargivemoney, -- 新增充值代金券金额（分成后）").append(" \n");
		sql.append("      weektotalfeenums, -- 当周充值人数").append(" \n");
		sql.append("      weektotalfee, -- 当周充值实付金额（分成前）").append(" \n");
		sql.append("      weeksharfee, -- 当周充值实付金额（分成后）").append(" \n");
		sql.append("      weektotalgivemoney, -- 当周充值代金券金额（分成前）").append(" \n");
		sql.append("      weekshargivemoney, -- 当周充值代金券金额（分成后）").append(" \n");
		sql.append("      monthtotalfeenums, -- 当月充值人数").append(" \n");
		sql.append("      monthtotalfee, -- 当月充值实付金额（分成前）").append(" \n");
		sql.append("      monthsharfee, -- 当月充值实付金额（分成后）").append(" \n");
		sql.append("      monthtotalgivemoney, -- 当月充值代金券金额（分成前）").append(" \n");
		sql.append("      monthshargivemoney, -- 当月充值代金券金额（分成后）").append(" \n");
		sql.append("      totalPayfeenums, -- 累计充值人数").append(" \n");
		sql.append("      totalPayshargivemoney, -- 累计充值代金券金额（分成后）").append(" \n");
		sql.append("      deduplicateDeviceCount, -- 去重设备数").append(" \n");
		sql.append("      returnDeviceCount, -- 回归设备数").append(" \n");
		sql.append("      duplicateDeviceCount, -- 重复设备数").append(" \n");
		sql.append("      payedRegisterCount, -- 付费注册数").append(" \n");
		sql.append("      payedRetention2Count, -- 付费留存数").append(" \n");
		sql.append("      payNum1, -- 首日付费次数").append(" \n");
		sql.append("      payNum7, -- 首周付费次数").append(" \n");
		sql.append("      roleDeviceCount, -- 创角的新增设备数").append(" \n");
		sql.append(" 	  newUserCount, -- 新增用户数").append(" \n");
		sql.append(" 	  createRoleCount, -- 新增注册创角数").append(" \n");
		sql.append(" 	  certifiedCount, -- 新增注册实名数").append(" \n");
		sql.append(" 	  notCertifiedCount, -- 注册未实名数").append(" \n");
		sql.append(" 	  periodPayCount, -- 期内设备付费数").append(" \n");
		sql.append("      periodPayFee1, -- 期内充值实付金额（分成前）").append(" \n");
		sql.append("      periodPayFee2, -- 期内充值实付金额（分成后）").append(" \n");
		sql.append("      periodPayGivemoney1, -- 期内充值代金券金额（分成前）").append(" \n");
		sql.append("      periodPayGivemoney2, -- 期内充值代金券金额（分成后）").append(" \n");
		sql.append(" 	  activedevices AS activeNum, -- 活跃设备数").append(" \n");
		sql.append("      payNum, -- 活跃付费次数").append(" \n");
		sql.append("      activepaydevices, -- 活跃付费设备数").append(" \n");
		sql.append("      activetotalfee, -- 活跃充值实付金额（分成前）").append(" \n");
		sql.append("      activesharfee, -- 活跃充值实付金额（分成后）").append(" \n");
		sql.append("      activetotalgivemoney, -- 活跃充值代金券金额（分成前）").append(" \n");
		sql.append("      activeshargivemoney, -- 活跃充值代金券金额（分成后）").append(" \n");
		sql.append(" 	  round(rudeCost, 2) rudeCost, -- 原始消耗").append(" \n");
		sql.append("      round(cost, 2) cost, -- 返点后消耗").append(" \n");
		sql.append("      shownums, -- 展示数").append(" \n");
		sql.append("      clicknums, -- 点击数").append(" \n");
		sql.append("      round(IF(usrpaynamenums > 0, divide(cost, usrpaynamenums), 0), 2) AS payCose1, -- 首日付费成本").append(" \n");
		sql.append("      round(IF(totalPayfeenums > 0, divide(cost, totalPayfeenums), 0), 2) AS payCoseAll, -- 累计付费成本").append(" \n");
		sql.append("     IF(showRatio = 1, round(newdevicesharfee, 2), round(newdevicefees, 2) ) AS newPayFee, -- 新增充值实付金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(newdeviceshargivemoney, 2), round(newdevicegivemoney, 2)) AS newPayGivemoney, -- 新增充值代金券金额").append(" \n");
		sql.append("     weektotalfeenums AS paydevice7, -- 当周付费设备数").append(" \n");
		sql.append("     IF(showRatio = 1, round(weeksharfee, 2), round(weektotalfee, 2)) AS weekPayFee, -- 当周充值实付金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(weekshargivemoney, 2), round(weektotalgivemoney, 2)) AS weekPayGivemoney, -- 当周充值代金券金额").append(" \n");
		sql.append("     monthtotalfeenums AS paydevice30, -- 当月付费设备数").append(" \n");
		sql.append("     IF(showRatio = 1, round(monthsharfee, 2), round(monthtotalfee, 2)) AS monthPayFee, -- 当月充值实付金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(monthshargivemoney, 2), round(monthtotalgivemoney, 2)) AS monthPayGivemoney, -- 当月充值代金券金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(periodPayFee2, 2), round(periodPayFee1, 2)) AS periodPayFee, -- 期内充值实付金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(periodPayGivemoney2, 2), round(periodPayGivemoney1, 2)) AS periodPayGivemoney, -- 期内充值代金券金额").append(" \n");
		sql.append("     totalPayfeenums AS paydeviceAll, -- 累计付费设备数").append(" \n");
		sql.append("     IF(showRatio = 1, round(totalPaysharfee, 2), round(totalPayfee, 2)) AS totalPayFee, -- 累计充值实付金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(totalPayshargivemoney, 2), round(totalPaygivemoney, 2)) AS totalPayGivemoney, -- 累计充值代金券金额").append(" \n");
		sql.append("     activepaydevices AS activePayCount, -- 活跃设备付费数").append(" \n");
		sql.append("     IF(showRatio = 1, round(activesharfee, 2), round(activetotalfee, 2)) AS activePayFee, -- 活跃充值实付金额").append(" \n");
		sql.append("     IF(showRatio = 1, round(activeshargivemoney, 2), round(activetotalgivemoney, 2)) AS activePayGivemoney, -- 活跃充值代金券金额").append(" \n");
		sql.append("     round(IF((shownums) > 0, divide(IF(clicknums > 0,clicknums,0) * 100.00, shownums), 0), 2) AS clickRatio, -- 点击率").append(" \n");
		sql.append("     round(IF((clicknums) > 0, divide(IF(usrnamenums > 0,usrnamenums,0) * 100.00, clicknums), 0), 2) AS regRatio, -- 点击注册率").append(" \n");
		sql.append("     round(IF((uuidnums) > 0, divide(IF(usrnamenums > 0,usrnamenums,0) * 100.00, uuidnums), 0), 2) AS activationRatio, -- 激活注册率").append(" \n");
		sql.append("     round(IF((usrnamenums) > 0, divide(cost, usrnamenums), 0), 2) AS deviceCose, -- 设备成本").append(" \n");
		sql.append("     round(IF((usrnamenums) > 0, divide(IF(createRoleCount > 0,createRoleCount,0) * 100.00, usrnamenums), 0), 2) AS createRoleRate, -- 新增注册创角率").append(" \n");
		sql.append("     round(IF((usrnamenums) > 0, divide(IF(certifiedCount > 0,certifiedCount,0) * 100.00, usrnamenums), 0), 2) AS certifiedRate, -- 新增实名制转化率").append(" \n");
		sql.append("     round(IF(usrnamenums > 0, divide(totalPayfeenums * 100.00, usrnamenums), 0), 2) AS totalPayRate, -- 累计付费率").append(" \n");
		sql.append("     round(IF((usrnamenums) > 0, divide(IF(retention2 > 0,retention2,0) * 100.00, usrnamenums), 0), 2) AS retention2Ratio, -- 次留").append(" \n");
		sql.append("     CAST(CASE WHEN payedRegisterCount > 0 THEN round(IF(payedRetention2Count > 0,payedRetention2Count,0) * 100 / IF(payedRegisterCount > 0, payedRegisterCount, 1), 2) ELSE 0 END AS DECIMAL(11, 2)) AS payedRetention2, -- 付费次留").append(" \n");
		sql.append("     round(IF((usrnamenums) > 0, divide(IF(usrpaynamenums > 0,usrpaynamenums,0) * 100.00, usrnamenums), 0), 2) AS regPayRatio, -- 新增付费率").append(" \n");
		sql.append("     IF(showRatio = 1, round(IF((usrnamenums) > 0, divide(newdevicesharfee, usrnamenums), 0), 2),round(IF((usrnamenums) > 0, divide(newdevicefees, usrnamenums), 0), 2)) AS regarpu, -- 新增注册ARPU").append(" \n");
		sql.append("     IF(showRatio = 1, round(IF((usrpaynamenums) > 0, divide(newdevicesharfee, usrpaynamenums), 0), 2),round(IF((usrpaynamenums) > 0, divide(newdevicefees, usrpaynamenums), 0), 2)) AS payarppu, -- 新增付费ARPPU").append(" \n");
		sql.append("     IF(showRatio = 1, round(if(cost > 0, divide(IF(newdevicesharfee > 0,newdevicesharfee,0) * 100.00, cost), 0), 2), round(IF(cost > 0, divide(IF(newdevicefees > 0,newdevicefees,0) * 100.00, cost), 0), 2)) roi1, -- 首日ROI").append(" \n");
		sql.append("     IF(showRatio = 1, round(if(cost > 0, divide(IF(weeksharfee > 0,weeksharfee,0) * 100.00, cost), 0), 2), round(IF(cost > 0, divide(IF(weektotalfee > 0,weektotalfee,0) * 100.00, cost), 0), 2)) weekRoi, -- 当周ROI").append(" \n");
		sql.append("     IF(showRatio = 1, round(if(cost > 0, divide(IF(monthsharfee > 0,monthsharfee,0) * 100.00, cost), 0), 2), round(IF(cost > 0, divide(IF(monthtotalfee > 0,monthtotalfee,0) * 100.00, cost), 0), 2)) monthRoi, -- 当月ROI").append(" \n");
		sql.append("     round(IF((usrnamenums) > 0, divide(IF(periodPayCount > 0,periodPayCount,0) * 100.00, usrnamenums), 0), 2) AS periodPayRate, -- 期内付费率").append(" \n");
		sql.append("     round(IF(cost > 0, divide(IF(showRatio = 1, round(periodPayFee2, 2), round(periodPayFee1, 2)) * 100.00, cost), 0), 2) AS periodROI, -- 期内ROI").append(" \n");
		sql.append("     IF(showRatio = 1, round(if(cost > 0, divide(IF(totalPaysharfee > 0,totalPaysharfee,0) * 100.00, cost), 0), 2), round(IF(cost > 0, divide(IF(totalPayfee > 0,totalPayfee,0) * 100.00, cost), 0), 2)) allRoi, -- 累计ROI").append(" \n");
		sql.append("     round(IF((activedevices) > 0, divide(IF(activepaydevices > 0,activepaydevices,0) * 100.00, activedevices), 0), 2) AS activePayRate, -- 活跃付费率").append(" \n");
		sql.append("     IF(showRatio = 1, round(IF((activedevices) > 0, divide(activesharfee, activedevices), 0), 2), round(IF((activedevices) > 0, divide(activetotalfee, activedevices), 0), 2)) actarpu, -- 活跃设备ARPU").append(" \n");
		sql.append("     IF(showRatio = 1, round(IF(activepaydevices > 0, divide(activesharfee, activepaydevices), 0), 2), round(IF(activepaydevices > 0, divide(activetotalfee, activepaydevices), 0), 2)) activearppu -- 活跃付费ARPPU").append(" \n");
		sql.append(" FROM").append(" \n");
		sql.append(" (").append(" \n");
		sql.append(this.getSql(req));
		sql.append(" ) res").append(" \n");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("adid")) {
				sql.append("    LEFT JOIN (\n");
				sql.append("        SELECT\n");
				sql.append("            adid adidTmp, budget budget, name adidName, campaign_id campaignId, campaign_name campaignName, start_time startTime, end_time endTime, create_time createTime, update_time updateTime, union_video_type unionVideoType, \n");
				sql.append("            inventory_type inventoryType, delivery_range deliveryRange, bid pricing, advertiser_id advertid, ad_account_name adAccountName, status status, opt_status optStatus, \n");
				sql.append("            ctype ctype\n");
				sql.append("        FROM ").append(yunYingDorisTableProperties.getVDimPanguAdidInfo3399()).append(" \n");
				sql.append("     where 1=1 ").append(this.directAdvertiseWhereSnippetOfStatus(req)).append(" \n");
				sql.append("    ) t2000 ON adid = t2000.adidTmp\n");
			}
		}
		return sql;
	}

	/**
	 * 筛选器选择广告计划状态时，过滤sql
	 * */
	private String directAdvertiseWhereSnippetOfStatus(AdOverviewDto2 req) {
		final StringBuilder whereSnippet = new StringBuilder();
		final String adStatusArr = req.getAdStatusArr();

		if (StringUtils.isNotBlank(adStatusArr)) {
			whereSnippet.append(" AND status IN ('").append(adStatusArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		return whereSnippet.toString();
	}

	private StringBuilder getSql(AdOverviewDto2 req) {
		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);

		StringBuilder arSql = getArSql(req);
		StringBuilder actSql = getActSql(req);
		StringBuilder costSql = getCostSql(req);

		StringBuilder fullJoinSelectFieldsSql = getFullJoinSelectFieldsSql(req, "ar", "act");
		StringBuilder fullJoinOnFieldsSql = getFullJoinOnFieldsSql(req, "ar", "act");
		StringBuilder fullJoinSelectFieldsSql2 = getFullJoinSelectFieldsSql(req, "arres", "costres");
		StringBuilder fullJoinOnFieldsSql2 = getFullJoinOnFieldsSql(req, "arres", "costres");

		StringBuilder sql = new StringBuilder();
		//所有指标full join
		//sql.append(" ( ").append("\n");
		sql.append(" select ").append("\n");
		sql.append(periodSql);
		sql.append(selectDimFieldsSql);
		sql.append(req.getShowRatio()).append(" as showRatio,").append("\n");
		sql.append("    SUM(usrnamenums) usrnamenums,-- 新增设备注册数 ").append("\n");
		sql.append("    SUM(uuidnums) uuidnums, -- 新增设备 ").append("\n");
		sql.append("    SUM(retention2) retention2, -- 次留 ").append("\n");
		sql.append("    SUM(usrpaynamenums) usrpaynamenums, -- 新增设备付费数 ").append("\n");
		sql.append("    SUM(newdevicefees) newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(newdevicesharfee) newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(newdevicegivemoney) newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(newdeviceshargivemoney) newdeviceshargivemoney, -- 新增充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(weektotalfeenums) weektotalfeenums, -- 当周充值人数 ").append("\n");
		sql.append("    SUM(weektotalfee) weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(weeksharfee) weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(weektotalgivemoney) weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(weekshargivemoney) weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(monthtotalfeenums) monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append("    SUM(monthtotalfee) monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(monthsharfee) monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(monthtotalgivemoney) monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(monthshargivemoney) monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(totalPayfeenums) totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append("    SUM(totalPayfee) totalPayfee, -- 累计充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(totalPaysharfee) totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(totalPaygivemoney) totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(totalPayshargivemoney) totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(deduplicateDeviceCount) deduplicateDeviceCount, -- 去重设备数 ").append("\n");
		sql.append("    SUM(returnDeviceCount) returnDeviceCount, -- 回归设备数 ").append("\n");
		sql.append("    SUM(duplicateDeviceCount) duplicateDeviceCount, -- 重复设备数 ").append("\n");
		sql.append("    SUM(payedRegisterCount) payedRegisterCount, -- 付费注册数 ").append("\n");
		sql.append("    SUM(payedRetention2Count) payedRetention2Count, -- 付费留存数 ").append("\n");
		sql.append("    SUM(payNum1) payNum1, -- 首日付费次数 ").append("\n");
		sql.append("    SUM(payNum7) payNum7, -- 首周付费次数 ").append("\n");
		sql.append("    SUM(roleDeviceCount) roleDeviceCount, -- 创角的新增设备数 ").append("\n");
		sql.append("    SUM(newUserCount) AS newUserCount, -- 新增用户数 ").append("\n");
		sql.append("    SUM(createRoleCount) AS createRoleCount, -- 新增注册创角数 ").append("\n");
		sql.append("    SUM(certifiedCount) AS certifiedCount, -- 新增注册实名数 ").append("\n");
		sql.append("    SUM(notCertifiedCount) AS notCertifiedCount, -- 注册未实名数 ").append("\n");
		sql.append("    SUM(periodPayCount) periodPayCount, -- 期内设备付费数 ").append("\n");
		sql.append("    SUM(periodPayFee1) periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(periodPayFee2) periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(periodPayGivemoney1) periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(periodPayGivemoney2) periodPayGivemoney2, -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(activedevices) activedevices, -- 活跃设备数 ").append("\n");
		sql.append("    SUM(payNum) as payNum, -- 活跃付费次数 ").append("\n");
		sql.append("    SUM(activepaydevices) activepaydevices, -- 活跃付费设备数 ").append("\n");
		sql.append("    SUM(activetotalfee) activetotalfee, -- 活跃充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(activesharfee) activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(activetotalgivemoney) activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(activeshargivemoney) activeshargivemoney, -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(rudeCost) rudeCost, -- 原始消耗 ").append("\n");
		sql.append("    SUM(cost) cost, -- 返点后消耗 ").append("\n");
		sql.append("    SUM(shownums) shownums, -- 展示数 ").append("\n");
		sql.append("    SUM(clicknums) clicknums -- 点击数 ").append("\n");
		sql.append(" FROM ").append("\n");
		sql.append(indentStr).append(" ( ").append("\n");
		sql.append(indentStr).append(" select ").append("\n");
		sql.append(indentStr).append(fullJoinSelectFieldsSql2).append("\n");
		sql.append(indentStr).append(" COALESCE(usrnamenums,0) usrnamenums, -- 新增设备注册数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(uuidnums,0) uuidnums, -- 新增设备 ").append("\n");
		sql.append(indentStr).append(" COALESCE(retention2,0) retention2, -- 次留 ").append("\n");
		sql.append(indentStr).append(" COALESCE(usrpaynamenums,0) usrpaynamenums, -- 新增设备付费数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdevicefees,0) newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdevicesharfee,0) newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdevicegivemoney,0) newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdeviceshargivemoney,0) newdeviceshargivemoney, -- 新增充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weektotalfeenums,0) weektotalfeenums, -- 当周充值人数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(weektotalfee,0) weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weeksharfee,0) weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weektotalgivemoney,0) weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weekshargivemoney,0) weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthtotalfeenums,0) monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthtotalfee,0) monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthsharfee,0) monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthtotalgivemoney,0) monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthshargivemoney,0) monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPayfeenums,0) totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPayfee,0) totalPayfee, -- 累计充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPaysharfee,0) totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPaygivemoney,0) totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPayshargivemoney,0) totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(deduplicateDeviceCount,0) deduplicateDeviceCount, -- 去重设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(returnDeviceCount,0) returnDeviceCount, -- 回归设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(duplicateDeviceCount,0) duplicateDeviceCount, -- 重复设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(payedRegisterCount,0) payedRegisterCount, -- 付费注册数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(payedRetention2Count,0) payedRetention2Count, -- 付费留存数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(payNum1,0)payNum1, -- 首日付费次数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(payNum7,0) payNum7, -- 首周付费次数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(roleDeviceCount,0) roleDeviceCount, -- 创角的新增设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(createRoleCount,0) createRoleCount, -- 新增注册创角数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(newUserCount,0) newUserCount,  -- 新增用户数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(certifiedCount,0) certifiedCount, -- 新增注册实名数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(notCertifiedCount,0) notCertifiedCount, -- 注册未实名数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(rudeCost,0) rudeCost, -- 原始消耗 ").append("\n");
		sql.append(indentStr).append(" COALESCE(cost,0) cost, -- 返点后消耗 ").append("\n");
		sql.append(indentStr).append(" COALESCE(shownums,0) shownums, -- 展示数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(clicknums,0) clicknums, -- 点击数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayCount,0) periodPayCount, -- 期内设备付费数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayFee1,0) periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayFee2,0) periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayGivemoney1,0) periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayGivemoney2,0) periodPayGivemoney2, -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activedevices,0) activedevices, -- 活跃设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(payNum,0)    payNum, -- 活跃付费次数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(activepaydevices,0) activepaydevices, -- 活跃付费设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(activetotalfee,0) activetotalfee,-- 活跃充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activesharfee,0) activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activetotalgivemoney,0) activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activeshargivemoney,0) activeshargivemoney -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(" from ").append("\n");
		sql.append(indentStr).append(" ( ").append("\n");
		sql.append(indentStr).append(" select ").append("\n");
		sql.append(indentStr).append(fullJoinSelectFieldsSql).append("\n");
		sql.append(indentStr).append(" usrnamenums,-- 新增设备注册数  ").append("\n");
		sql.append(indentStr).append(" uuidnums, -- 新增设备  ").append("\n");
		sql.append(indentStr).append(" retention2, -- 次留  ").append("\n");
		sql.append(indentStr).append(" usrpaynamenums, -- 新增设备付费数  ").append("\n");
		sql.append(indentStr).append(" newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" newdeviceshargivemoney, -- 新增充值代金券金额（分成后）  ").append("\n");
		sql.append(indentStr).append(" weektotalfeenums, -- 当周充值人数  ").append("\n");
		sql.append(indentStr).append(" weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append(indentStr).append(" monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append(indentStr).append(" totalPayfee, -- 累计充值实付金额（分成前）  ").append("\n");
		sql.append(indentStr).append(" totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" deduplicateDeviceCount, -- 去重设备数  ").append("\n");
		sql.append(indentStr).append(" returnDeviceCount, -- 回归设备数 ").append("\n");
		sql.append(indentStr).append(" duplicateDeviceCount, -- 重复设备数  ").append("\n");
		sql.append(indentStr).append(" payedRegisterCount, -- 付费注册数  ").append("\n");
		sql.append(indentStr).append(" payedRetention2Count, -- 付费留存数 ").append("\n");

		sql.append(indentStr).append(" payNum1, -- 首日付费次数 ").append("\n");
		sql.append(indentStr).append(" payNum7, -- 首周付费次数  ").append("\n");
		sql.append(indentStr).append(" roleDeviceCount, -- 创角的新增设备数 ").append("\n");

		sql.append(indentStr).append(" newUserCount, -- 新增用户数  ").append("\n");
		sql.append(indentStr).append(" createRoleCount, -- 新增注册创角数  ").append("\n");
		sql.append(indentStr).append(" certifiedCount, -- 新增注册实名数  ").append("\n");
		sql.append(indentStr).append(" (newUserCount - certifiedCount ) as notCertifiedCount, -- 注册未实名数  ").append("\n");
		sql.append(indentStr).append(" activedevices, -- 活跃设备数  ").append("\n");

		sql.append(indentStr).append(" payNum, -- 活跃付费次数  ").append("\n");

		sql.append(indentStr).append(" activepaydevices, -- 活跃付费设备数  ").append("\n");
		sql.append(indentStr).append(" activetotalfee,-- 活跃充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" activeshargivemoney, -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" periodPayCount, -- 期内设备付费数  ").append("\n");
		sql.append(indentStr).append(" periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" periodPayGivemoney2 -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" from ").append("\n");
		sql.append(arSql);
		sql.append(indentStr).append(" full join ").append("\n");
		sql.append(actSql);
		sql.append(indentStr).append(" on ").append(fullJoinOnFieldsSql).append("\n");
		sql.append(indentStr).append(" ) arres ").append("\n");
		sql.append(indentStr).append(" full join ").append("\n");
		sql.append(costSql);
		sql.append(indentStr).append(" on ").append(fullJoinOnFieldsSql2).append("\n");
		sql.append(" ) res ").append("\n");
		sql.append(" group by ").append("\n");
		sql.append(periodSql);
		sql.append(groupByFieldsSql).append("\n");
		return sql;
	}

	/**
	 *  指标子查询sql：新增、付费、期内、角色账号
	 * */
	private StringBuilder getArSql(AdOverviewDto2 req){
		Long rsTime = req.getRsTime();
		Long reTime = req.getReTime();
		Long startPayDate = req.getStartPayDate();
		Long endPayDate = req.getEndPayDate();

		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder whereFieldsSql = getWhereFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr1).append(" ( ").append("\n");
		sql.append(indentStr1).append(" select ").append("\n");
		//日期字段+维度字段
		sql.append(indentStr1).append(periodSql).append(selectDimFieldsSql);
		//指标字段
		sql.append(indentStr1).append(" bitmap_union_count(reg_device_bitmap) usrnamenums,-- 新增设备注册数 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(new_device_bitmap) uuidnums, -- 新增设备数 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(active_device_bitmap_1) retention2, -- 次留 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(new_device_pay_bitmap) usrpaynamenums, -- 新增设备付费数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_fee_1),0) newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_fee_1*sharing),0) newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_givemoney_1),0) newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_givemoney_1*sharing),0) newdeviceshargivemoney, -- 新增充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" 0 as weektotalfeenums, -- 当周充值人数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_week),0) weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_week * sharing), 0) weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_week),0) weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_week * sharing), 0) weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" 0 as monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_month), 0) monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_month * sharing), 0) monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_month), 0) monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_month * sharing), 0) monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(active_device_pay_bitmap), 0) totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_fee), 0) totalPayfee, -- 累计充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_fee * sharing), 0) totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_givemoney), 0) totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_givemoney * sharing), 0) totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(deduplicate_device_bitmap), 0) deduplicateDeviceCount, -- 去重设备数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(return_device_bitmap), 0) returnDeviceCount, -- 回归设备数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(duplicate_device_bitmap), 0) duplicateDeviceCount, -- 重复设备数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE( bitmap_union_count(BITMAP_AND(reg_device_bitmap,new_device_pay_bitmap)), 0 ) payedRegisterCount, -- 付费注册数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_count(BITMAP_AND(bitmap_union(active_device_bitmap_1),bitmap_union(new_device_pay_bitmap))), 0) payedRetention2Count, -- 付费留存数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_pay_num_1), 0) payNum1, -- 首日付费次数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(if(daysDiff<7,new_pay_num_1,0)), 0) payNum7, -- 首周付费次数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(create_role_bitmap), 0) AS roleDeviceCount, -- 创角的新增设备数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(create_role_num), 0) AS createRoleCount, -- 新增注册创角数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(reg_account_bitmap), 0) AS newUserCount, -- 新增用户数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(certi_account_bitmap), 0) AS certifiedCount, -- 新增注册实名数  ").append("\n");
		//sql.append(indentStr1).append(" 0 AS notCertifiedCount, -- 注册未实名数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count( if( ").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_device_pay_bitmap,bitmap_empty() ) ), 0) periodPayCount, -- 期内设备付费数 ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_fee,0) ) periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_fee * sharing,0) ) periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_givemoney,0) ) periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_givemoney * sharing,0) ) periodPayGivemoney2 -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" from ").append("\n");
		sql.append(indentStr2).append(" ( ").append("\n");
		sql.append(indentStr2).append(" select ").append("\n");
		sql.append(indentStr2).append(" ar.reg_day as day,ar.active_day,days_diff(ar.active_day,ar.reg_day) daysDiff,ar.reg_week as week,ar.reg_month as month,'汇总' as collect,ar.media_code,ar.chl_app as appchl,ar.ad_id as adid, ").append("\n");
		sql.append(indentStr2).append(" dimchl.sharing as sharing,dimchl.chl_main as parentchl,dimchl.game_sub as gameid,dimchl.game_main as pgid,dimchl.game_sub_name,dimchl.game_main_name,dimchl.os as os, ").append("\n");
		sql.append(indentStr2).append(" dimchl.investor as investor,dimchl.investor_name as investorName,dimchl.dept_id as deptId,dimchl.dept_name as deptName,dimchl.dept_group_id as userGroupId,dimchl.dept_group_name as userGroupName, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.account_id advertiserid,dimadchl.convert_name convertName,dimadchl.deep_convert deepConvert,dimadchl.status status, dimchl.spread_type spread_type, ").append("\n");
		sql.append(indentStr2).append(" reg_device_bitmap, -- 新增设备注册数、付费注册数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" new_device_bitmap, -- 新增设备 ").append("\n");
		sql.append(indentStr2).append(" active_device_bitmap_1, -- 次留、付费留存数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" new_device_pay_bitmap, -- 新增设备付费数、付费留存数（二次计算）、付费注册数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" new_fee_1, -- 新增充值实付金额（分成前）新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_givemoney_1, -- 新增充值代金券金额（分成前） / 新增充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_fee_week, -- 当周充值实付金额（分成前）/ 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_givemoney_week, -- 当周充值代金券金额（分成前）/ 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_fee_month, -- 当月充值实付金额（分成前）、 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_givemoney_month,-- 当月充值代金券金额（分成前）/ 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_device_pay_bitmap, -- 累计充值人数/期内设备付费数/活跃付费设备数 ").append("\n");
		sql.append(indentStr2).append(" total_fee, -- 累计充值实付金额（分成前）、累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" total_givemoney, -- 累计充值代金券金额（分成前）、累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" deduplicate_device_bitmap, -- 去重设备数 ").append("\n");
		sql.append(indentStr2).append(" return_device_bitmap, -- 回归设备数 ").append("\n");
		sql.append(indentStr2).append(" duplicate_device_bitmap, -- 重复设备数 ").append("\n");
		sql.append(indentStr2).append(" new_pay_num_1, -- 首日付费次数 、 首周付费次数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" create_role_bitmap, -- 创角的新增设备数 ").append("\n");
		sql.append(indentStr2).append(" create_role_num , -- 新增注册创角数 ").append("\n");
		sql.append(indentStr2).append(" certi_device_bitmap,-- 新增实名设备数 ").append("\n");
		sql.append(indentStr2).append(" reg_account_bitmap,-- 新增设备注册账号数 ").append("\n");
		sql.append(indentStr2).append(" certi_account_bitmap,-- 新增注册实名账号数 ").append("\n");
		sql.append(indentStr2).append(" active_fee, -- 期内充值实付金额（分成前）/ 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_givemoney -- 期内充值代金券金额（分成前）/ 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" from ").append("\n");
//		sql.append(indentStr3).append(" ( ").append("\n");
////		sql.append(indentStr3).append(" select ").append("\n");
////		sql.append(indentStr3).append(" reg_day,active_day,days_diff(active_day,reg_day) daysDiff,reg_week,reg_month,media_code,chl_app,ad_id,creative_id, ").append("\n");
////		sql.append(indentStr3).append(" reg_device_bitmap, -- 新增设备注册数、付费注册数（二次计算） ").append("\n");
////		sql.append(indentStr3).append(" new_device_bitmap, -- 新增设备 ").append("\n");
////		sql.append(indentStr3).append(" active_device_bitmap_1, -- 次留、付费留存数（二次计算） ").append("\n");
////		sql.append(indentStr3).append(" new_device_pay_bitmap, -- 新增设备付费数、付费留存数（二次计算）、付费注册数（二次计算） ").append("\n");
////		sql.append(indentStr3).append(" new_fee_1, -- 新增充值实付金额（分成前）新增充值实付金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" new_givemoney_1, -- 新增充值代金券金额（分成前） / 新增充值代金券金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" new_fee_week, -- 当周充值实付金额（分成前）/ 当周充值实付金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" new_givemoney_week, -- 当周充值代金券金额（分成前）/ 当周充值代金券金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" new_fee_month, -- 当月充值实付金额（分成前）、 当月充值实付金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" new_givemoney_month,-- 当月充值代金券金额（分成前）/ 当月充值代金券金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" active_device_pay_bitmap, -- 累计充值人数 ").append("\n");
////		sql.append(indentStr3).append(" total_fee, -- 累计充值实付金额（分成前）、累计充值实付金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" total_givemoney, -- 累计充值代金券金额（分成前）、累计充值代金券金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" deduplicate_device_bitmap, -- 去重设备数 ").append("\n");
////		sql.append(indentStr3).append(" return_device_bitmap, -- 回归设备数 ").append("\n");
////		sql.append(indentStr3).append(" duplicate_device_bitmap, -- 重复设备数 ").append("\n");
////		sql.append(indentStr3).append(" new_pay_num_1, -- 首日付费次数 、 首周付费次数（二次计算） ").append("\n");
////		sql.append(indentStr3).append(" create_role_bitmap, -- 创角的新增设备数 ").append("\n");
////		sql.append(indentStr3).append(" create_role_num , -- 新增注册创角数 ").append("\n");
////		sql.append(indentStr3).append(" certi_device_bitmap,-- 新增实名设备数 ").append("\n");
////		sql.append(indentStr3).append(" reg_account_bitmap,-- 新增设备注册账号数  ").append("\n");
////		sql.append(indentStr3).append(" certi_account_bitmap,-- 新增注册实名账号数 ").append("\n");
////		sql.append(indentStr3).append(" active_fee, -- 期内充值实付金额（分成前）/ 期内充值实付金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" active_givemoney, -- 期内充值代金券金额（分成前）/ 期内充值代金券金额（分成后） ").append("\n");
////		sql.append(indentStr3).append(" active_device_bitmap, -- 活跃设备数 ").append("\n");
////		sql.append(indentStr3).append(" active_pay_num -- 活跃付费次数 ").append("\n");
//		sql.append(indentStr3).append(" from ");
		sql.append(indentStr3).append(dataLayerOfflineRepairDao.getAdsOperation3399AdReportSql(rsTime,reTime,yunYingDorisTableProperties.getREG_DAY(), DataReportEnum.ADANALYSIS.getType())).append("\n");
//		sql.append(indentStr3).append(" ) ar ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399ChannelGameInvestor()).append(" dimchl ").append("\n");
		sql.append(indentStr3).append(" on ar.chl_app = dimchl.chl_app ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399AdChannel()).append(" dimadchl ").append("\n");
		sql.append(indentStr3).append(" on ar.ad_id = dimadchl.ad_id and ar.media_code = dimadchl.media_code ").append("\n");
		//sql.append(indentStr3).append(" where 1=1  ").append("\n");
		sql.append(indentStr2).append(" ) ar  ").append("\n");
		sql.append(indentStr2).append(" where 1=1  ").append("\n");
		//过滤字段
		sql.append(indentStr2).append(whereFieldsSql).append("\n");
		sql.append(indentStr2).append(" group by ").append("\n");
		//日期分组字段+维度分组字段
		sql.append(indentStr2).append(periodSql).append(groupByFieldsSql).append("\n");
		sql.append(indentStr1).append(" ) ar ").append("\n");
		return sql;
	}

	/**
	 * 活跃指标
	 * */
	private StringBuilder getActSql(AdOverviewDto2 req){
		Long rsTime = req.getRsTime();
		Long reTime = req.getReTime();

		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder whereFieldsSql = getWhereFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr1).append(" ( ").append("\n");
		sql.append(indentStr1).append(" select ").append("\n");
		//日期字段+维度字段
		sql.append(indentStr1).append(periodSql).append(selectDimFieldsSql);
		//指标字段
		sql.append(indentStr1).append(" bitmap_union_count(active_device_bitmap) activedevices, -- 活跃设备数 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(active_device_pay_bitmap) activepaydevices, -- 活跃付费设备数 ").append("\n");
		sql.append(indentStr1).append(" SUM(active_fee) activetotalfee, -- 活跃充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_fee * sharing) activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_givemoney) activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_givemoney * sharing) activeshargivemoney, -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_pay_num) payNum -- 活跃付费次数 ").append("\n");
		sql.append(indentStr1).append(" from ").append("\n");
		sql.append(indentStr2).append(" ( ").append("\n");
		sql.append(indentStr2).append(" select ").append("\n");
		sql.append(indentStr2).append(" ar.active_day as day,ar.week as week,ar.month as month,'汇总' as collect,ar.media_code,ar.chl_app as appchl,ar.ad_id as adid, ").append("\n");
		sql.append(indentStr2).append(" dimchl.sharing as sharing,dimchl.chl_main as parentchl,dimchl.game_sub as gameid,dimchl.game_main as pgid,dimchl.game_sub_name,dimchl.game_main_name,dimchl.os as os, ").append("\n");
		sql.append(indentStr2).append(" dimchl.investor as investor,dimchl.investor_name as investorName,dimchl.dept_id as deptId,dimchl.dept_name as deptName,dimchl.dept_group_id as userGroupId,dimchl.dept_group_name as userGroupName, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.account_id advertiserid,dimadchl.convert_name convertName,dimadchl.deep_convert deepConvert,dimadchl.status status, dimchl.spread_type spread_type, ").append("\n");
		sql.append(indentStr2).append(" active_device_bitmap, -- 活跃设备数 ").append("\n");
		sql.append(indentStr2).append(" active_device_pay_bitmap, -- 活跃付费设备数 ").append("\n");
		sql.append(indentStr2).append(" active_fee, -- 活跃充值实付金额（分成前）/ 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_givemoney, -- 活跃充值代金券金额（分成前）/ 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_pay_num -- 活跃付费次数 ").append("\n");
		sql.append(indentStr2).append(" from ").append("\n");
//		sql.append(indentStr3).append(" ( ").append("\n");
//		sql.append(indentStr3).append(" select ").append("\n");
//		sql.append(indentStr3).append(" active_day,week,month,reg_day,reg_week,reg_month,media_code,chl_app,ad_id,creative_id, ").append("\n");
//		sql.append(indentStr3).append(" active_device_bitmap, -- 活跃设备数 ").append("\n");
//		sql.append(indentStr3).append(" active_device_pay_bitmap, -- 活跃付费设备数 ").append("\n");
//		sql.append(indentStr3).append(" active_fee, -- 活跃充值实付金额（分成前）/ 活跃充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_givemoney, -- 活跃充值代金券金额（分成前）/ 活跃充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_pay_num -- 活跃付费次数 ").append("\n");
//		sql.append(indentStr3).append(" from ");
		sql.append(indentStr3).append(dataLayerOfflineRepairDao.getAdsOperation3399AdReportSql(rsTime,reTime,yunYingDorisTableProperties.getACTIVE_DAY(), DataReportEnum.ADANALYSIS.getType())).append("\n");
		//日期过滤
//		sql.append(indentStr3).append(" where active_day BETWEEN ").append(" to_date(").append(rsTime).append(")").append(" and ")
//				.append(" to_date(").append(reTime).append(")").append("\n");
//		sql.append(indentStr3).append(" ) ar ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399ChannelGameInvestor()).append(" dimchl ").append("\n");
		sql.append(indentStr3).append(" on ar.chl_app = dimchl.chl_app ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399AdChannel()).append(" dimadchl ").append("\n");
		sql.append(indentStr3).append(" on ar.ad_id = dimadchl.ad_id and ar.media_code = dimadchl.media_code ").append("\n");
		//sql.append(indentStr3).append(" where 1=1  ").append("\n");
		sql.append(indentStr2).append(" ) act  ").append("\n");
		sql.append(indentStr2).append(" where 1=1  ").append("\n");
		//过滤字段
		sql.append(indentStr2).append(whereFieldsSql).append("\n");
		sql.append(indentStr2).append(" group by ").append("\n");
		//日期分组字段+维度分组字段
		sql.append(indentStr2).append(periodSql).append(groupByFieldsSql).append("\n");
		sql.append(indentStr1).append(" ) act ").append("\n");
		return sql;
	}

	/**
	 *  指标子查询sql：消耗
	 * */
	private StringBuilder getCostSql(AdOverviewDto2 req){
		Long rsTime = req.getRsTime();
		Long reTime = req.getReTime();

		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder whereFieldsSql = getWhereFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr1).append(" ( ").append("\n");
		sql.append(indentStr1).append(" select ").append("\n");
		//日期字段+维度字段
		sql.append(indentStr1).append(periodSql).append(selectDimFieldsSql);
		//指标字段
		sql.append(indentStr1).append(" COALESCE(SUM(cost), 0) rudeCost, -- 原始消耗 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(round((cost*100)/(rebate+100) ,3)), 0) cost, -- 返点后消耗 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(ad_show), 0) shownums, -- 展示数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(ad_click), 0) clicknums -- 点击数 ").append("\n");
		sql.append(indentStr1).append(" from ").append("\n");
		sql.append(indentStr2).append(" ( ").append("\n");
		sql.append(indentStr2).append(" select ").append("\n");
		sql.append(indentStr2).append(" cost.day as day,cost.week as week,cost.month as month,'汇总' as collect,cost.date_type,cost.media_code,cost.account_id as advertiserid,cost.ad_id as adid, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.investor as investor,dimadchl.investor_name as investorName,dimadchl.dept_id as deptId,dimadchl.dept_name as deptName,dimadchl.dept_group_id as userGroupId,dimadchl.dept_group_name as userGroupName, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.rebate,dimadchl.game_main as pgid,dimadchl.game_sub as gameid,dimadchl.chl_main as parentchl,dimadchl.chl_app as appchl,dimadchl.os as os, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.convert_name as convertName,dimadchl.deep_convert as deepConvert,dimadchl.status status, dimadchl.spread_type spread_type,").append("\n");
		sql.append(indentStr2).append(" ad_show, -- 展示数 ").append("\n");
		sql.append(indentStr2).append(" ad_click, -- 点击数 ").append("\n");
		sql.append(indentStr2).append(" cost -- 消耗 ").append("\n");
		sql.append(indentStr2).append(" from ").append("\n");
		sql.append(indentStr3).append(" ( ").append("\n");
		sql.append(indentStr3).append(" select ").append("\n");
		sql.append(indentStr3).append(" day,month,week,date_type,media_code,account_id,campaign_id,ad_id,creative_id, ").append("\n");
		sql.append(indentStr3).append(" ad_show, -- 展示数 ").append("\n");
		sql.append(indentStr3).append(" ad_click, -- 点击数 ").append("\n");
		sql.append(indentStr3).append(" cost -- 消耗 ").append("\n");
		sql.append(indentStr3).append(" from ").append(yunYingDorisTableProperties.getDwsOperation3399AdCostD()).append("\n");
		//日期过滤
		sql.append(indentStr3).append(" where day BETWEEN ").append(" to_date(").append(rsTime).append(")").append(" and ")
				.append(" to_date(").append(reTime).append(")").append("\n");
		sql.append(indentStr3).append(" ) cost ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399AdChannel()).append(" dimadchl ").append("\n");
		sql.append(indentStr3).append(" on cost.ad_id = dimadchl.ad_id and cost.media_code = dimadchl.media_code ").append("\n");
		//sql.append(indentStr3).append(" where 1=1  ").append("\n");
		sql.append(indentStr2).append(" ) cost  ").append("\n");
		sql.append(indentStr2).append(" where 1=1  ").append("\n");
		//过滤字段
		sql.append(indentStr2).append(whereFieldsSql).append("\n");
		sql.append(indentStr2).append(" group by ").append("\n");
		//日期分组字段+维度分组字段
		sql.append(indentStr2).append(periodSql).append(groupByFieldsSql).append("\n");
		sql.append(indentStr1).append(" ) costres ").append("\n");
		return sql;
	}


	/**
	 * where Fields：注册时间、活跃时间、主渠道、广告账户、广告计划、转化目标、深度转化目标、主游戏、子游戏、部门、投放组、投放人、系统、代理商、分包、广告计划状态
	 * */
	private StringBuilder getWhereFieldsSql(AdOverviewDto2 req) {
		final String parentchlArr = req.getParentchlArr();
		final String agentIdArr = req.getAgentIdArr();
		final String agentAdvertiserIdArr = req.getAgentAdvertiserIdArr();
		final String advertiserIdArr = req.getAdvertiserIdArr();
		final String adidArr = req.getAdidArr();
		final String convertArr = req.getConvertArr();
		final String deepConvertArr = req.getDeepConvertArr();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String deptIdArr = req.getDeptIdArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final String investorArr = req.getInvestorArr();
		final Integer os = req.getOs();
		final String appchlArr = req.getAppchlArr();
		//筛选器选择广告计划状态时
		final String adStatusArr = req.getAdStatusArr();

		StringBuilder sql = new StringBuilder();
		sql.append(" and spread_type <> 2 ");
		if (StringUtils.isNotBlank(adStatusArr)) {
			sql.append(" AND status IN ('").append(adStatusArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (os != null) {
			sql.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			sql.append(" AND pgid IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			sql.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			sql.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			sql.append(" AND appchl IN ('").append(appchlArr.replaceAll(",", "', '")).append("')");
		}
		if (StringUtils.isNotBlank(adidArr)) {
			sql.append(" AND adid IN ('").append(adidArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(advertiserIdArr)) {
			sql.append(" AND advertiserid IN ('").append(advertiserIdArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(agentIdArr) && StringUtils.isNotBlank(agentAdvertiserIdArr)) {
			sql.append(" AND advertiserid IN ('").append(agentAdvertiserIdArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(agentIdArr) && StringUtils.isBlank(agentAdvertiserIdArr)) {
			sql.append(" AND advertiserid IN (' ").append("')");
		}
		if (StringUtils.isNotBlank(deptIdArr)) {
			sql.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			sql.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			sql.append(" AND investor IN (").append(investorArr).append(")");
		}
		if (StringUtils.isNotBlank(convertArr)) {
			sql.append(" AND convertName IN ('").append(convertArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if ("empty".equals(deepConvertArr)) {
			sql.append(" AND (deepConvert = '' OR deepConvert IS NULL)");
		} else if (StringUtils.isNotBlank(deepConvertArr)) {
			sql.append(" AND deepConvert = '").append(deepConvertArr).append("'");
		}
		// 账号渠道权限
		if (req.getIsSys() != 1) {
			sql.append(" AND investor IN (").append(req.getUserIds()).append(")");
		}
		// 广告权限
		if (req.getIsSys() != 1) {
			sql.append(" AND advertiserid IN (").append(req.getAdAccounts()).append(")");
		}
		return sql;
	}

	/**
	 * groupby Fields
	 * 渠道、转化目标、广告账户、广告计划、主游戏、子游戏、分包、系统、部门、投放人、组别
	 * */
	private StringBuilder getGroupByFieldsSql(AdOverviewDto2 req) {
		StringBuilder groupColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		if (StringUtils.isNotBlank(queryColumn)) {
			groupColumnSql.append(",");
			groupColumnSql.append(queryColumn);
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(", deptName");
			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(", investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(", userGroupName");
			}
			if (queryColumn.contains("convertName")) {
				groupColumnSql.append(", deepConvert");
			}
		}
		return groupColumnSql;
	}

	/**
	 * select dim Fields
	 * */
	private StringBuilder getSelectDimFieldsSql(AdOverviewDto2 req) {
		StringBuilder queryColumnSql = new StringBuilder();
		//无论是否有分组项，必定有一个","
		queryColumnSql.append(",");
		String queryColumn = req.getQueryColumn();
		if (StringUtils.isNotBlank(queryColumn)) {
			queryColumnSql.append(queryColumn).append(",");
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName, ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName, ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName, ");
			}
			if (queryColumn.contains("convertName")) {
				queryColumnSql.append("deepConvert, ");
			}
			queryColumnSql.append("\n");
		}
		return queryColumnSql;
	}

	/**
	 *  select Period field
	 * */
	private StringBuilder getPeriodSql(AdOverviewDto2 req) {
		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}

	/**
	 *  full join select fields
	 * */
	private StringBuilder getFullJoinSelectFieldsSql(AdOverviewDto2 req, String left, String right){
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		queryColumnSql.append("COALESCE(").append(left).append(".").append(this.getPeriodSql(req)).append(",").append(right).append(".").append(this.getPeriodSql(req)).append(") ").append(this.getPeriodSql(req)).append(",");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("parentchl")) {
				queryColumnSql.append("COALESCE(").append(left).append(".parentchl,").append(right).append(".parentchl) parentchl,");
			}
			if (queryColumn.contains("convertName")) {
				queryColumnSql.append("COALESCE(").append(left).append(".deepConvert,").append(right).append(".deepConvert) deepConvert,COALESCE(").append(left).append(".convertName,").append(right).append(".convertName) convertName,");
			}
			if (queryColumn.contains("advertiserid")) {
				queryColumnSql.append("COALESCE(").append(left).append(".advertiserid,").append(right).append(".advertiserid) advertiserid,");
			}
			if (queryColumn.contains("adid")) {
				queryColumnSql.append("COALESCE(").append(left).append(".adid,").append(right).append(".adid) adid,");
			}
			if (queryColumn.contains("pgid")) {
				queryColumnSql.append("COALESCE(").append(left).append(".pgid,").append(right).append(".pgid) pgid,");
			}
			if (queryColumn.contains("gameid")) {
				queryColumnSql.append("COALESCE(").append(left).append(".gameid,").append(right).append(".gameid) gameid,");
			}
			if (queryColumn.contains("appchl")) {
				queryColumnSql.append("COALESCE(").append(left).append(".appchl,").append(right).append(".appchl) appchl,");
			}
			if (queryColumn.contains("os")) {
				queryColumnSql.append("COALESCE(").append(left).append(".os,").append(right).append(".os) os,");
			}
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("COALESCE(").append(left).append(".deptId,").append(right).append(".deptId) deptId,COALESCE(").append(left).append(".deptName,").append(right).append(".deptName) deptName,");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("COALESCE(").append(left).append(".investor,").append(right).append(".investor) investor,COALESCE(").append(left).append(".investorName,").append(right).append(".investorName) investorName,");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("COALESCE(").append(left).append(".userGroupId,").append(right).append(".userGroupId) userGroupId,COALESCE(").append(left).append(".userGroupName,").append(right).append(".userGroupName) userGroupName,");
			}
		}
		sql.append(queryColumnSql);
		return sql;
	}

	/**
	 * full join  on fields
	 * */
	private StringBuilder getFullJoinOnFieldsSql(AdOverviewDto2 req, String left, String right){
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		//日期
		queryColumnSql.append(left).append(".").append(this.getPeriodSql(req)).append(" = ").append(right).append(".").append(this.getPeriodSql(req)).append(" ");
		if (StringUtils.isNotBlank(queryColumn)) {
			//渠道
			if (queryColumn.contains("parentchl")) {
				queryColumnSql.append("AND ").append(left).append(".parentchl = ").append(right).append(".parentchl ");
			}
			if (queryColumn.contains("convertName")) {
				queryColumnSql.append("AND ").append(left).append(".convertName = ").append(right).append(".convertName ");
			}
			if (queryColumn.contains("deepConvert")) {
				queryColumnSql.append("AND ").append(left).append(".deepConvert = ").append(right).append(".deepConvert ");
			}
			if (queryColumn.contains("advertiserid")) {
				queryColumnSql.append("AND ").append(left).append(".advertiserid = ").append(right).append(".advertiserid ");
			}
			if (queryColumn.contains("adid")) {
				queryColumnSql.append("AND ").append(left).append(".adid = ").append(right).append(".adid ");
			}
			if (queryColumn.contains("pgid")) {
				queryColumnSql.append("AND ").append(left).append(".pgid = ").append(right).append(".pgid ");
			}
			if (queryColumn.contains("gameid")) {
				queryColumnSql.append("AND ").append(left).append(".gameid = ").append(right).append(".gameid ");
			}
			if (queryColumn.contains("appchl")) {
				queryColumnSql.append("AND ").append(left).append(".appchl = ").append(right).append(".appchl ");
			}
			if (queryColumn.contains("os")) {
				queryColumnSql.append("AND ").append(left).append(".os = ").append(right).append(".os ");
			}

			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("AND ").append(left).append(".deptId = ").append(right).append(".deptId ");
				queryColumnSql.append("AND ").append(left).append(".deptName = ").append(right).append(".deptName ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("AND ").append(left).append(".investor = ").append(right).append(".investor ");
				queryColumnSql.append("AND ").append(left).append(".investorName = ").append(right).append(".investorName ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("AND ").append(left).append(".userGroupId = ").append(right).append(".userGroupId ");
				queryColumnSql.append("AND ").append(left).append(".userGroupName = ").append(right).append(".userGroupName ");
			}
		}
		sql.append(queryColumnSql);
		return sql;
	}

}
